package com.house365.im.client.db;

import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.house365.core.util.TextUtil;
import com.house365.core.util.db.DataBaseOpenHelper;
import com.house365.core.util.db.DataBaseOpenHelper.DataBaseOpenListener;
import com.house365.core.util.db.DataBaseService;
import com.house365.core.util.db.DataBaseService.DBQuery;
import com.house365.im.client.dto.MessageDto;

/**
 * @author py
 * @date 2013-5-22
 * @comment
 */
public class MessageDBService {
	protected DataBaseService dbService;
	private static MessageDBService instance;

	public MessageDBService(Context context) {
		dbService = new DataBaseService(context, "im", 2,
				new DataBaseOpenListener() {

					@Override
					public void onVersionUpgrade(SQLiteDatabase db, int arg1,
							int arg2) {
						// TODO Auto-generated method stub
					    if(arg2 ==2 && arg1==1){
					        String updateSqlV1_2 = "alter table message add subject TEXT";
					        db.execSQL(updateSqlV1_2);
					    }
					}

					@Override
					public String[] getIndexSql() {
						String[] indexsqlArray = {
								"CREATE INDEX [IDX_MESSAGE_FROMUSER] ON [message]([fromuser]  ASC)",
								"CREATE INDEX [IDX_MESSAGE_TOUSER] ON [message]([touser]  ASC)",
								"CREATE INDEX [IDX_MESSAGE_STARTTIME] ON [message]([starttime]  DESC)" };
						return indexsqlArray;
					}

					@Override
					public String[] getCreateSql() {
						String[] createSql = new String[1];
						createSql[0] = "CREATE TABLE IF NOT EXISTS message (id integer primary key autoincrement,starttime long,fromuser varchar(100), touser varchar(100),message text, status int,type int,subject TEXT)";
						return createSql;
					}
				});
		
		updateSendMessageStatusFail();
	}

	public static MessageDBService getDBService(Context context) {
		if (instance == null)
			instance = new MessageDBService(context);
		return instance;
	}

	public LinkedList<MessageDto> getMessageDtos(String you, String me,
			long starttime, int firstResult, int maxResult) {
		LinkedList<MessageDto> ls = (LinkedList<MessageDto>) dbService
				.query("select * from message where  ((fromuser= ? and  touser =? ) or ( fromuser= ? and  touser =? ))"
						+ (starttime > 0 ? " and starttime<" + starttime : "")
						+ " order by  starttime  desc limit ?,?",
						new String[] { you, me, me, you,
								String.valueOf(firstResult),
								String.valueOf(maxResult) }, new DBQuery() {

							@Override
							public Object onQueryResult(Cursor cursor) {
								LinkedList<MessageDto> MessageDtos = new LinkedList<MessageDto>();
								while (cursor.moveToNext()) {
									MessageDto MessageDto = new MessageDto();
									MessageDto.readFromCursor(cursor);
									MessageDtos.add(MessageDto);
								}
								return MessageDtos;
							}
						});
		Collections.reverse(ls);
		return ls;
	}

	private List<String> getContactHistoryFrom(String me) {
		return (List<String>) dbService.query(
				"select DISTINCT(touser) from message where  fromuser= ? ",
				new String[] { me }, new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						LinkedList<String> contacters = new LinkedList<String>();
						while (cursor.moveToNext()) {
							contacters.add(cursor.getString(0));
						}
						return contacters;
					}
				});
	}

	private List<String> getContactHistoryTo(String me) {
		return (List<String>) dbService.query(
				"select DISTINCT(fromuser) from message where  touser= ? ",
				new String[] { me }, new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						LinkedList<String> contacters = new LinkedList<String>();
						while (cursor.moveToNext()) {
							contacters.add(cursor.getString(0));
						}
						return contacters;
					}
				});
	}
	public  List<String> getContactHistory(String me){
		List<String> froms = getContactHistoryTo(me);
		List<String> tos = getContactHistoryFrom(me);
		List<String> temp = new ArrayList<String>();
		temp.addAll(froms);
		for(String t:tos){
			if(!froms.contains(t)){
				temp.add(t);
			}
		}
		return temp;
		
	}
	/**
	 * 设置接受消息状态为已读
	 * @param dots
	 */
	public void setMessageReaded(LinkedList<MessageDto> dots) {
		for (MessageDto dto : dots) {
			setMessageReaded(dto);
		}
	}
	public void setMessageReaded(MessageDto dto) {
		String sql = "update message set status = " +  MessageDto.STATUS_OK + " where id ="
				+ dto.getId()+" and status = "+MessageDto.STATUS_UNREAD;
		dbService.execute(sql, new Object[] {});
	}
	public void setMessageReaded(String you, String me) {
		String sql = "update message set status = " +  MessageDto.STATUS_OK + " where fromuser= ? and  touser =? and status ="+MessageDto.STATUS_UNREAD;
		dbService.execute(sql, new String[] {you,me,});
	}
		
	public void updateSendMessageStatus(MessageDto dto, int status) {
		String sql = "update message set status = " + status + " where id ="
				+ dto.getId();
		dbService.execute(sql, new Object[] {});
	}
	
	public void updateSendMessageStatusFail() {
		String sql = "update message set status = " + MessageDto.STATUS_SEND_ERROR + " where status ="
				+ MessageDto.STATUS_UNSEND;
		dbService.execute(sql, new Object[] {});
	}

	public void saveMessageDtos(LinkedList<MessageDto> datas) {
		try {
			DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
			SQLiteDatabase db = dbo.getWritableDatabase();
			db.beginTransaction();
			for (MessageDto messageDto : datas) {
				db.execSQL(
						"insert into message(starttime,fromuser, touser,message, status,type, subject)values(?,?,?,?,?,?,?)",
						new Object[] { messageDto.getStarttime(),
								TextUtil.getUTF8(messageDto.getFromuser()),
								TextUtil.getUTF8(messageDto.getTouser()),
								TextUtil.getUTF8(messageDto.getMessage()),
								messageDto.getStatus(), messageDto.getType(),
								TextUtil.getUTF8(messageDto.getSubject())});
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void saveMessageDto(MessageDto messageDto) {

		dbService
				.execute(
						"insert into message(starttime,fromuser, touser,message, status,type,subject)values(?,?,?,?,?,?,?)",
						new Object[] { messageDto.getStarttime(),
								TextUtil.getUTF8(messageDto.getFromuser()),
								TextUtil.getUTF8(messageDto.getTouser()),
								TextUtil.getUTF8(messageDto.getMessage()),
								messageDto.getStatus(), messageDto.getType() ,TextUtil.getUTF8(messageDto.getSubject())});

	}
	public int saveMessageDtoWithId(MessageDto messageDto) {

		dbService
				.execute(
						"insert into message(starttime,fromuser, touser,message, status,type,subject)values(?,?,?,?,?,?,?)",
						new Object[] { messageDto.getStarttime(),
								TextUtil.getUTF8(messageDto.getFromuser()),
								TextUtil.getUTF8(messageDto.getTouser()),
								TextUtil.getUTF8(messageDto.getMessage()),
								messageDto.getStatus(), messageDto.getType(),
								TextUtil.getUTF8(messageDto.getSubject()) });
		return (Integer)dbService.query("select id from message where  fromuser= ? and  touser =? and starttime = ?", new String[]{
				messageDto.getFromuser(),messageDto.getTouser(),messageDto.getStarttime()+""
		}, new DBQuery() {
			@Override
			public Object onQueryResult(Cursor cursor) {
				int result = -1;
				if (cursor.moveToNext()) {
					result =cursor.getInt(0);
				}
				return result;
			}
		});

	}
	public void removeMessageDto(int id) {
		dbService.execute("delete from  message where id  =  " + id,
				new Object[] {});
	}

	public MessageDto getMessageDtoById(int id) {
		return (MessageDto) dbService.query(
				"select * from message where id = ?", new String[] { id + "" },
				new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						MessageDto MessageDto = null;
						if (cursor.moveToNext()) {
							MessageDto = new MessageDto();
							MessageDto.readFromCursor(cursor);
						}
						return MessageDto;
					}
				});
	}



	public MessageDto getLastMessageDtoSignle(String from, String to) {
		return (MessageDto) dbService.query(
				"select * from message where  fromuser= ? and  touser =? "
						+ " order by  starttime  desc limit 0,1", new String[] {
						from, to }, new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						MessageDto messageDto = null;
						if (cursor.moveToNext()) {
							messageDto = new MessageDto();
							messageDto.readFromCursor(cursor);
						}
						return messageDto;
					}
				});
	}

	public int getUnreadMessageCountByFromUser(String you, String me) {
		return (Integer) dbService.query(
				"select count(*) from message where fromuser = ? and touser = ? and status ="
						+ MessageDto.STATUS_UNREAD, new String[] { you, me },
				new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						if (cursor.moveToNext()) {
							return cursor.getInt(0);
						}
						return 0;
					}
				});
	}
	
	public int getUnreadMessageCountByFromUserAndSubject(String you, String me,String subject) {
	    return (Integer) dbService.query(
	            "select count(*) from message where fromuser = ? and touser = ? and subject = ? and status ="
	                    + MessageDto.STATUS_UNREAD, new String[] { you, me ,subject},
	                    new DBQuery() {
	                        
	                        @Override
	                        public Object onQueryResult(Cursor cursor) {
	                            if (cursor.moveToNext()) {
	                                return cursor.getInt(0);
	                            }
	                            return 0;
	                        }
	                    });
	}
	public int getUnreadMessageCount(String me ) {
		return (Integer) dbService.query(
				"select count(*) from message where status ="
						+ MessageDto.STATUS_UNREAD+"  and touser = ?", new String[] { me },
				new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						if (cursor.moveToNext()) {
							return cursor.getInt(0);
						}
						return 0;
					}
				});
	}
	
	
	public MessageDto getLastMessageDtoBoth(String you, String me) {
		return (MessageDto) dbService
				.query("select * from message where  ((fromuser= ? and  touser =? ) or ( fromuser= ? and  touser =? ))"
						+" order by  starttime  desc limit 0,1", new String[] {
						you, me, me, you }, new DBQuery() {

					@Override
					public Object onQueryResult(Cursor cursor) {
						MessageDto messageDto = null;
						if (cursor.moveToNext()) {
							messageDto = new MessageDto();
							messageDto.readFromCursor(cursor);
						}
						return messageDto;
					}
				});
	}
	public void clear() {
		dbService.execute("delete from message", new Object[] {});
	}
	
	public void updateMessageStatusByTime(long time, int status) {
		String sql = "update message set status = " + status + " where starttime ="
				+ time;
		dbService.execute(sql, new Object[] {});
	}
	
    public void removeMessageByFromUser(String fromUser) {
        dbService.execute("delete from  message where fromuser  =  ?", new Object[] { fromUser });
    }
}
